﻿using System;
using System.Collections.Generic;
using System.Text;
using Dapper;
using System.Data;
using System.Threading.Tasks;
using BCEntity.CRM.CustomerReceptionRecord;
using System.Linq;
using BCEntity.Sim.Admin.Admin;
using BCEntity.Sim.Company.Department;
using BCEntity.Sim.Admin.StaffOrganizationMappingRecord;
using BCCommon;

namespace BCData.CRM.CustomerReceptionRecord
{
    public  class CustomerReceptionRecordData : ICustomerReceptionRecordData
    {
        private IDatabaseContext database;
        private readonly string cSql = @"SELECT CustomerReceptionRecordId,CompanyId,ArrivalTime,ExitTime,ReceptionType,ReceptionTimesType,UserName,Phone,Email,Sex,CustomerType,CustomerLevel,PreestimateBookTime,ProvinceCode,ProvinceName,CityCode,CityName,CountyCode,CountyName,Address,Remark,AdminId,AdminFullName,IsArchive,ArchiveTime FROM  crm_customerreceptionrecord   ";
        #region 构造函数
        public CustomerReceptionRecordData(IDatabaseContext databaseContext)
        {
            this.database = databaseContext;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新纪录
        /// </summary>
        /// <param name="CustomerReceptionRecordEntity"></param>
        /// <returns></returns>
        public async Task<CustomerReceptionRecordEntity> InsertAsync(CustomerReceptionRecordEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }
        #endregion

        #region updateAsync
        /// <summary>
        /// 根据主键ID更新记录
        /// </summary>
        /// <param name="CustomerReceptionRecordEntity"></param>
        /// <returns></returns>
        public async Task<CustomerReceptionRecordEntity> UpdateAsync(CustomerReceptionRecordEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }
        #endregion 

        #region Delete
        /// <summary>
        /// 删除指定ID的记录
        /// </summary>
        /// <param name="customerReceptionRecordId"></param>
        /// <returns></returns>
        public async Task<bool> Delete(long customerReceptionRecordId)
        {
            string fsql = " DELETE FROM crm_customerreceptionrecord WHERE CustomerReceptionRecordId =?CustomerReceptionRecordId ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CustomerReceptionRecordId", customerReceptionRecordId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(fsql, parameters,database.Transaction)>0;
        }
        #endregion

        #region GetById
        /// <summary>
        /// 获取指定ID的记录
        /// </summary>
        /// <param name="customerReceptionRecordId"></param>
        /// <returns></returns>
        public async Task<CustomerReceptionRecordEntity> GetById(long customerReceptionRecordId)
        {
            string fSql = cSql +  "  WHERE  CustomerReceptionRecordId =?CustomerReceptionRecordId ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CustomerReceptionRecordId", customerReceptionRecordId, DbType.Int64); 
            return await this.database.Slave.QueryFirstOrDefaultAsync<CustomerReceptionRecordEntity>(fSql, parameters);
        }
        #endregion


        #region GetList  
        public async Task<(int ccCount, IEnumerable<CustomerReceptionRecordEntity>)> GetListAsync(
                            AdminEntity ccAdminEntity, 
                            DateTime? ccArrivalTime1,
                            DateTime? ccArrivalTime2,
                            DateTime? ccExitTime1,
                            DateTime? ccExitTime2,
                            int companyId,
                            string ccUserName,
                            string ccPhone,
                            string ccEmail,
                            string ccProvinceId,
                            string ccCityId,
                            string ccCountyId,
                            int pageNo,
                            int pageSize )
            { 
            string fSqlWhere = string.Empty;
            DynamicParameters parameters = new DynamicParameters();
            if (ccAdminEntity.IsSystemAccount)
            {
                fSqlWhere = " WHERE 1=1";
            }
            else
            {
                foreach (var item in GetListAdminId(ccAdminEntity))
                {
                    fSqlWhere += item + ",";
                }
                fSqlWhere = " WHERE  ADMINID IN ( " + fSqlWhere.TrimEnd(',') + " )";
            }

            fSqlWhere += $" AND CompanyId={companyId}";

            if (!string.IsNullOrEmpty(ccProvinceId))
            {
                fSqlWhere += string.Format(" AND provinceid = '{0}'", ccProvinceId);
            }
            if (!string.IsNullOrWhiteSpace(ccCityId))
            {
                fSqlWhere += " AND cityid = ?CityCode ";
                parameters.Add("?CityCode", ccCityId, DbType.Int64);
            }

            if (ccArrivalTime1 != null && ccArrivalTime2 != null)
            {
                fSqlWhere += " AND ArrivalTime BETWEEN ?ArrivalTime1 AND  ?ArrivalTime2 ";

                parameters.Add("?ArrivalTime1", ccArrivalTime1.Value, DbType.DateTime);
                parameters.Add("?ArrivalTime2", ccArrivalTime2.Value.AddDays(1), DbType.DateTime);
            }
            if (ccExitTime1 != null && ccExitTime2 != null)
            {
                fSqlWhere += " AND ExitTime BETWEEN ?ExitTime1 AND ?ExitTime2 ";
                parameters.Add("?ExitTime1", ccExitTime1.Value, DbType.DateTime);
                parameters.Add("?ExitTime2", ccExitTime2.Value.AddDays(1), DbType.DateTime);

            }
            if (!string.IsNullOrEmpty(ccUserName))
            {
                fSqlWhere += string.Format(" AND UserName LIKE '%{0}%'", ccUserName);
            }
            if (!string.IsNullOrEmpty(ccPhone))
            {
                fSqlWhere += string.Format(" AND phone LIKE '%{0}%'", ccPhone);
            }
            if (!string.IsNullOrEmpty(ccEmail))
            {
                fSqlWhere += string.Format(" AND Email LIKE '%{0}%'", ccEmail);
            } 
            string fSqlCount = "SELECT COUNT(1) FROM crm_customerreceptionrecord " +fSqlWhere;
            string fSql = cSql + fSqlWhere + string.Format(" ORDER BY CustomerReceptionRecordId DESC  LIMIT {0},{1} ; ", (pageNo - 1) * pageSize, pageSize);

            var fRet = await this.database.Slave.QueryMultipleAsync<CustomerReceptionRecordEntity, int>(fSql + fSqlCount, parameters);
            return (fRet.Item2, fRet.Item1.ToList());
        }

        private List<long> GetListAdminId(AdminEntity ccAdminEntity)
        {
            string fSql = "SELECT * FROM department ";
            IEnumerable<DepartmentEntity> fListDepartMent = this.database.Slave.QueryAsync<DepartmentEntity>(fSql).GetAwaiter().GetResult();
            var fDepartmentEntity = fListDepartMent.FirstOrDefault(s => s.PrincipalAdminId == ccAdminEntity.AdminId);

            List<long> fListAdminId = new List<long>();
            if (fDepartmentEntity == null)
            {
                fListAdminId.Add(ccAdminEntity.AdminId);
            }
            else
            {
                fListAdminId = GetListAdminId(GetDepartmentID(fDepartmentEntity, fListDepartMent));
            }
            return fListAdminId;
        }

        private List<int> GetDepartmentID(DepartmentEntity ccDepartmentEntity, IEnumerable<DepartmentEntity> ccListDepartmentEntity)
        {
            List<int> fListDepartmentId = new List<int>();
            fListDepartmentId.Add(ccDepartmentEntity.DepartmentId);
            foreach (var item in ccListDepartmentEntity.Where(s => s.ParentDepartmentId == ccDepartmentEntity.DepartmentId))
            {
                fListDepartmentId.Add(item.DepartmentId);
                foreach (var item2 in ccListDepartmentEntity.Where(s => s.ParentDepartmentId == item.DepartmentId))
                {
                    fListDepartmentId.Add(item2.DepartmentId);
                    foreach (var item3 in ccListDepartmentEntity.Where(s => s.ParentDepartmentId == item2.DepartmentId))
                    {
                        fListDepartmentId.Add(item3.DepartmentId);
                        foreach (var item4 in ccListDepartmentEntity.Where(s => s.ParentDepartmentId == item3.DepartmentId))
                        {
                            fListDepartmentId.Add(item4.DepartmentId);
                        }
                    }
                }
            }
            return fListDepartmentId;
        }

        private List<long> GetListAdminId(IEnumerable<int> ccListDepartmentId)
        {
            string fSql = " SELECT AdminId FROM StaffOrganizationMappingRecord WHERE departmentid IN  ";

            string fStr = string.Empty;
            foreach (var item in ccListDepartmentId)
            {
                fStr += item + ",";
            }
            fStr = "(" + fStr.TrimEnd(',') + ")";
            fSql += fStr;

            IEnumerable<StaffOrganizationMappingRecordEntity> fListDepartMentStaff = this.database.Slave.QueryAsync<StaffOrganizationMappingRecordEntity>(fSql).GetAwaiter().GetResult();
            List<long> ccListAdminId = fListDepartMentStaff.Select(p => p.AdminId).ToList();
            return ccListAdminId;
        }
        #endregion
    }
}
